package csc.fresher.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Repository;

import csc.fresher.dao.PeriodTypeDao;
import csc.fresher.entity.PeriodType;
import csc.fresher.utils.DBUtils;

@Component
public class PeriodTypeDaoImpl implements PeriodTypeDao{
	private static final Logger logger = Logger.getLogger(PeriodTypeDaoImpl.class);
	
	@Autowired
	DataSource dataSource;
	
	@Override
	public void insert(PeriodType periodType) {
		String query = "INSERT INTO `periodtype` (`SavingPeriodType`,`SavingPeriod`,`Unit`) VALUES (?,?,?)";

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setString(1, periodType.getSavingPeriodType());
			preparedStatement.setInt(2, periodType.getSavingPeriod());
			preparedStatement.setString(3, periodType.getUnit());
			preparedStatement.execute();
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DBUtils.closeConnection(connection, preparedStatement, logger);
		}
	}

	@Override
	public void update(PeriodType periodType) {
		String query = "UPDATE `periodtype` "
				+ " SET `periodtype`.`SavingPeriodType` = ?, `periodtype`.`SavingPeriod` = ?, `periodtype`.`Unit` = ?"
				+ " WHERE PeriodTypeID = ?";

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setString(1, periodType.getSavingPeriodType());
			preparedStatement.setInt(2, periodType.getSavingPeriod());
			preparedStatement.setString(3, periodType.getUnit());
			preparedStatement.setInt(4, periodType.getPeriodTypeID());
			preparedStatement.execute();
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DBUtils.closeConnection(connection, preparedStatement, logger);
		}
		
	}

	@Override
	public PeriodType getPeriodTypeByID(int periodTypeID) {
		String query = "SELECT * FROM `periodtype` WHERE `periodtype`.`PeriodTypeID`=?";
		PeriodType periodType = null;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setLong(1, periodTypeID);
			rs = preparedStatement.executeQuery();
			if(rs.next())
				periodType = getPeriodTypeResultSet(rs);
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DBUtils.closeResultSetConnection(connection, preparedStatement, rs, logger);
		}
		return periodType;
	}

	@Override
	public List<PeriodType> getAllPeriodTypes() {
		String query = "SELECT * FROM `periodtype`";
		List<PeriodType> periodTypes = new ArrayList<PeriodType>();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query);
			rs = preparedStatement.executeQuery();
			while(rs.next())
				periodTypes.add(getPeriodTypeResultSet(rs));
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DBUtils.closeResultSetConnection(connection, preparedStatement, rs, logger);
		}
		return periodTypes;
	}

	@Override
	public List<PeriodType> getListPeriodTypes(int startIndex, int maxReturn) {
		String query = "SELECT * FROM `periodtype` LIMIT  ?, ?";
		List<PeriodType> periodTypes = new ArrayList<PeriodType>();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setInt(1, startIndex);
			preparedStatement.setInt(2, maxReturn);
			rs = preparedStatement.executeQuery();
			while(rs.next())
				periodTypes.add(getPeriodTypeResultSet(rs));
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DBUtils.closeResultSetConnection(connection, preparedStatement, rs, logger);
		}
		return periodTypes;
	}

	@Override
	public int totalPeriodTypes() {
		String query = "SELECT Count(*) FROM `periodtype`";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query);
			rs = preparedStatement.executeQuery();
			if(rs.next())
				return rs.getInt(1);
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DBUtils.closeResultSetConnection(connection, preparedStatement, rs, logger);
		}
		return 0;
	}

	private PeriodType getPeriodTypeResultSet(ResultSet rs) throws SQLException{
		PeriodType periodType = new PeriodType();
		periodType.setPeriodTypeID(rs.getInt("PeriodTypeID"));
		periodType.setSavingPeriodType(rs.getString("SavingPeriodType"));
		periodType.setSavingPeriod(rs.getInt("SavingPeriod"));
		periodType.setUnit(rs.getString("Unit"));
		return periodType;
	}
	
}
